﻿using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Packaging;
using System.Linq;
using System.Text.RegularExpressions;
using System.Xml.Linq;

namespace Kivensoft.XlsReader {
	public enum NumberFormat {
		General, Number, Currency, Accounting, Date, Time, DateTime,
		Percentage, Fraction, Scientific, Text, Special, Custom,
		Unsupported
	};

	public class WorkbookOptions {
		public bool IncludeHidden { get; set; }
		public bool LoadSheets { get; set; }

		public WorkbookOptions() {
			IncludeHidden = false;
			LoadSheets = true;
		}
	}

	public class Workbook {
		private const string NS_MAIN = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
		private const string NS_PR = "http://schemas.openxmlformats.org/package/2006/relationships";
		private const string NS_OR = "http://schemas.openxmlformats.org/officeDocument/2006/relationships";
		private const string NS_ORW = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet";

		private IList<Sheet> sheets = new List<Sheet>();
		private IDictionary<int, string> sharedStrings = new Dictionary<int, string>();
		private IDictionary<int, NumberFormat> numberFormats = new Dictionary<int, NumberFormat>();
		private WorkbookOptions options = new WorkbookOptions();
		private int baseYear = 1900;

		public Workbook(string fileName, WorkbookOptions options = null) {
			if (options != null) this.options = options;
			try {
				using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.Read)) {
					Open(stream);
				}
			}
			catch (Exception ex) {
				throw new Exception("打开文件失败！", ex);
			}
		}

		public Workbook(Stream input, WorkbookOptions options = null) {
			if (input == null || !input.CanRead)
				throw new Exception("读取文件流失败！");
			if (options != null) this.options = options;
			Open(input);
		}

		private void Open(Stream stream) {
			try {
				using (Package archive = Package.Open(stream, FileMode.Open, FileAccess.Read)) {
					var sheetEntry = archive.GetPart(new Uri("/xl/workbook.xml", UriKind.Relative));
					LoadWorkbookXml(sheetEntry.GetStream());

					var sheetPathsEntry = archive.GetPart(new Uri("/xl/_rels/workbook.xml.rels", UriKind.Relative));
					LoadWorkbookXmlRels(sheetPathsEntry.GetStream());

					var sharedStringsEntry = archive.GetPart(new Uri("/xl/sharedStrings.xml", UriKind.Relative));
					LoadSharedStrings(sharedStringsEntry == null ? null : sharedStringsEntry.GetStream());

					var stylesEntry = archive.GetPart(new Uri("/xl/styles.xml", UriKind.Relative));
					LoadStyles(stylesEntry.GetStream());

					// Optionally load all sheets
					if (options.LoadSheets) {
						foreach (Sheet sheet in sheets) {
							sheet.Open(archive);
						}
					}
				}

			}
			catch (Exception ex) {
				throw new Exception("文件格式错误！", ex);
			}
		}

		private void LoadWorkbookXml(Stream stream) {
			XDocument document = XDocument.Load(stream);
			XElement root = document.Root;
			XNamespace ns = NS_MAIN;
			XNamespace r = NS_OR;

			XElement workbookPr = root.Element(ns + "workbookPr");
			if (workbookPr != null) {
				XAttribute date1904 = workbookPr.Attribute("date1904");
				if (date1904 != null && date1904.Value == "1") {
					baseYear = 1904;
				}
			}

			foreach (XElement element in root.Element(ns + "sheets").Elements()) {
				XAttribute id = element.Attribute(r + "id");
				XAttribute name = element.Attribute("name");
				XAttribute state = element.Attribute("state");

				bool hidden = false;
				if (state != null && state.Value == "hidden") {
					hidden = true;
				}

				Sheet sheet = new Sheet(name.Value, id.Value, hidden);
				sheet.Workbook = this;
				sheets.Add(sheet);
			}
		}

		private void LoadWorkbookXmlRels(Stream stream) {
			XDocument document = XDocument.Load(stream);
			XElement root = document.Root;
			XNamespace ns = NS_PR;

			foreach (XElement element in root.Elements(ns + "Relationship")) {
				XAttribute id = element.Attribute("Id");
				XAttribute type = element.Attribute("Type");
				XAttribute target = element.Attribute("Target");

				if (type.Value != NS_ORW) continue;

				string idv = id.Value;
				Sheet sheet = sheets.SingleOrDefault(v => v.Id == idv);

				// Get sheet file path
				Match match = Regex.Match(target.Value, @"worksheets/(.+)");
				string path = @"xl/worksheets/" + match.Groups[1].Value;
				sheet.Path = path.ToLower();
			}
		}

		private void LoadSharedStrings(Stream stream) {
			// The xl/sharedStrings.xml file will be missing if there are no strings
			if (stream == null) return;

			XDocument document = XDocument.Load(stream);
			XElement root = document.Root;
			XNamespace ns = NS_MAIN;
			int count = 0;

			foreach (XElement si in root.Elements(ns + "si")) {
				IEnumerable<XElement> ts = si.Descendants(ns + "t");
				string sum = string.Empty;
				foreach (XElement t in ts) sum += t.Value;

				sharedStrings.Add(count, sum);
				count++;
			}
		}

		private void LoadStyles(Stream stream) {
			XDocument document = XDocument.Load(stream);
			XNamespace ns = NS_MAIN;
			XElement cellXfs = document.Root.Element(ns + "cellXfs");
			int index = 0;

			foreach (XElement element in cellXfs.Elements()) {
				XAttribute numFmtId = element.Attribute("numFmtId");
				if (numFmtId != null) {
					int numberFormatId = int.Parse(numFmtId.Value);
					NumberFormat nf = NumberFormat.General;
					switch (numberFormatId) {
						case 0: nf = NumberFormat.General; break;
						case 2: nf = NumberFormat.Number; break;
						case 164: nf = NumberFormat.Currency; break;
						case 44: nf = NumberFormat.Accounting; break;
						case 14: nf = NumberFormat.Date; break;
						case 165: nf = NumberFormat.Time; break;
						case 22: nf = NumberFormat.DateTime; break;
						case 49: nf = NumberFormat.Text; break;
						case 10: nf = NumberFormat.Percentage; break;
						case 13: nf = NumberFormat.Fraction; break;
						case 166: nf = NumberFormat.Custom; break;
						case 11: nf = NumberFormat.Scientific; break;
						default: nf = NumberFormat.Unsupported; break;
					}
					numberFormats.Add(index++, nf);
				}
			}
		}

		public IList<Sheet> Sheets { get { return sheets; } }

		public Sheet Sheet(string name) {
			return sheets.SingleOrDefault(s => s.Name == name);
		}

		public WorkbookOptions Options {
			get { return options; }
			set { options = value; }
		}

		public IDictionary<int, string> SharedStrings {
			get { return sharedStrings; }
		}

		public IDictionary<int, NumberFormat> NumberFormats {
			get { return numberFormats; }
		}

		public int BaseYear {
			get { return baseYear; }
		}
	}
}
